import java.sql.*;
import java.util.Scanner;

//TIP To <b>Run</b> code, press <shortcut actionId="Run"/> or
// click the <icon src="AllIcons.Actions.Execute"/> icon in the gutter.
public class Main {
    public static void main(String[] args) {

        //查询
        m1();
        //条件查询
        //  m2()
        //增加
        //  m3()
        //删除
        m4();
        //修改
       //m5();

    }
    private static void m5(){
        try {
            Scanner sc=new Scanner(System.in);


            Class.forName("com.mysql.cj.jdbc.Driver");
            //2.获取连接(url,user,password)
            String url = "jdbc:mysql://127.0.0.1:3306/jdbc?characterEncoding=utf-8";
            Connection con = DriverManager.getConnection(url, "root", "Aa123456");
            String sql= "UPDATE sys_user SET name = ? ,password=? where id= ?";

            PreparedStatement updateStatement = con.prepareStatement(sql);
            //参数设置


            updateStatement.setObject(1, "helly");

            updateStatement.setObject(2, "12345");
            updateStatement.setObject(3, "3");

            int result=updateStatement.executeUpdate();
            if(result>0){
                System.out.println("修改成功");
            }
            updateStatement.close();
            con.close();

        }
        catch (ClassNotFoundException e) {
            throw new RuntimeException(e);
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }


        }

    private static void m4() {
        //1.加载驱动
        try {

            Class.forName("com.mysql.cj.jdbc.Driver");
            //2.获取连接(url,user,password)
            String url = "jdbc:mysql://127.0.0.1:3306/jdbc?characterEncoding=utf-8";
            Connection con = DriverManager.getConnection(url, "root", "Aa123456");

            //3.写sql语句
            String sql="delete from sys_user where id=?";

            //4.预编译sql
            PreparedStatement pst = con.prepareStatement(sql);

            //5.有参数设置参数
            pst.setObject(1,"2");//推荐用
            //6.执行sql语句 更新
            int result=pst.executeUpdate();

            //7.结果判断
            if(result>0){
                System.out.println("删除成功");
            }


            //8.关闭资源
            pst.close();
            con.close();

        } catch (ClassNotFoundException e) {
            throw new RuntimeException(e);
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }

    private static void m3() {
        //1.加载驱动
        try {
            //Scanner
            Scanner sc=new Scanner(System.in);
            System.out.println("请输入新增的姓名：");
            String name=sc.next();
            System.out.println("请输入新增的密码：");
            String pass=sc.next();
            Class.forName("com.mysql.cj.jdbc.Driver");
            //2.获取连接(url,user,password)
            String url = "jdbc:mysql://127.0.0.1:3306/jdbc?characterEncoding=utf-8";
            Connection con = DriverManager.getConnection(url, "root", "Aa123456");

            //3.写sql语句
            String sql="insert into sys_user(name,pass) values(?,?)";

            //4.预编译sql
            PreparedStatement pst = con.prepareStatement(sql);

            //5.有参数设置参数
            pst.setObject(1,name);
            pst.setObject(2,pass);

            //6.执行sql语句 更新
            int result=pst.executeUpdate();

            //7.结果判断
            if(result>0){
                System.out.println("新增成功");
            }
            //8.关资源
            pst.close();
            con.close();

        } catch (ClassNotFoundException e) {
            throw new RuntimeException(e);
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }


    }

    private static void m2() {
        //1.加载驱动
        try {
            //Scanner
            Scanner sc=new Scanner(System.in);
            System.out.println("请输入你要查询的姓名：");
            String word=sc.next();

            Class.forName("com.mysql.cj.jdbc.Driver");
            //2.获取连接(url,user,password)
            String url = "jdbc:mysql://127.0.0.1:3306/jdbc?characterEncoding=utf-8";
            Connection con = DriverManager.getConnection(url, "root", "Aa123456");
            //3.写sql语句
            String sql="select * from sys_user where name like ?";

            //4.预编译sql
            PreparedStatement pst = con.prepareStatement(sql);

            //5.有参数设置参数
            pst.setObject(1,"%"+word+"%");
            //6.执行sql语句
            ResultSet resultSet = pst.executeQuery();

            //7.遍历结果集
            while(resultSet.next()){
                int id = resultSet.getInt(1);
                String name = resultSet.getString(2);
                String pass = resultSet.getString(3);
                System.out.println(id+"-----"+name+"-----"+pass);
            }

            //8.关资源
            resultSet.close();
            pst.close();
            con.close();
        } catch (ClassNotFoundException e) {
            throw new RuntimeException(e);
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }

    }

    private static void m1() {
        //1.加载驱动
        try {
            Class.forName("com.mysql.cj.jdbc.Driver");
            //2.获取连接(url,user,password)
            String url = "jdbc:mysql://127.0.0.1:3306/jdbc?characterEncoding=utf-8";
            Connection con = DriverManager.getConnection(url, "root", "Aa123456");

            //3.写sql语句
            String sql="select * from sys_user";

            //4.预编译sql
            PreparedStatement pst = con.prepareStatement(sql);

            //5.有参数设置参数

            //6.执行sql语句
            ResultSet resultSet = pst.executeQuery();

            //7.遍历结果集
            while(resultSet.next()){
                int id = resultSet.getInt(1);
                String name = resultSet.getString(2);
                String pass = resultSet.getString(3);
                System.out.println(id+"--"+name+"--"+pass);
            }

            //8.关资源
            resultSet.close();
            pst.close();
            con.close();
        } catch (ClassNotFoundException e) {
            throw new RuntimeException(e);
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }

    }
}